/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package models.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import models.DBHandler;
import models.beans.CoursesUserBean;

/**
 *
 * @author Group 3 Aprotrain Aptech  Group3 Aprotrain Aptech
 */
public class CoursesUserDao {
    public static List<CoursesUserBean> getAllUsersByCourses() throws ClassNotFoundException, SQLException {
        List<CoursesUserBean> list = new ArrayList<CoursesUserBean>();
        ResultSet rs = null;
        Connection conn = DBHandler.getConnection();
        PreparedStatement pstm = conn.prepareStatement("SELECT dbo.tblUser.userName, dbo.tblCoursesUser.userId, dbo.tblCoursesUser.coursesId, dbo.tblCourses.coursesName FROM dbo.tblCourses INNER JOIN dbo.tblCoursesUser ON dbo.tblCourses.coursesId = dbo.tblCoursesUser.coursesId INNER JOIN dbo.tblUser ON dbo.tblCoursesUser.userId = dbo.tblUser.userId");
        rs = pstm.executeQuery();
        CoursesUserBean courses = null;
        while (rs.next()) {
            courses = new CoursesUserBean();
            courses.setCoursesName(rs.getString("coursesName"));
            courses.setUserName(rs.getString("userName"));
            courses.setUserId(rs.getString("userId"));
            courses.setCoursesId(rs.getInt("coursesId"));
            list.add(courses);
        }
        DBHandler.closeDBObj(rs);
        DBHandler.closeDBObj(pstm);
        DBHandler.closeDBObj(conn);

        return list;
    }
    public static int deleteCoursesUser(int userId) throws ClassNotFoundException, SQLException {
        int result = 0;

        Connection oconn = DBHandler.getConnection();
        PreparedStatement pstmt = oconn.prepareStatement("DELETE FROM tblCoursesUser WHERE userId=?");
        pstmt.setInt(1, userId);
        result = pstmt.executeUpdate();
        DBHandler.closeDBObj(pstmt);
        DBHandler.closeDBObj(oconn);
        return result;
    }

}
